/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.registration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.commons.Const;
import vn.smartware.co.dto.MstRoomDTO;
import vn.smartware.co.dto.MstRoomPriceDTO;
import vn.smartware.co.forms.RegisterRoomForm;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.sql.factory.AbstractRegisterRoomSQLManager;

/**
 * @author thuctt
 * RegisterRoomSQLManager
 */
public class RegisterRoomSQLManager extends AbstractRegisterRoomSQLManager {

	private static Log log = LogFactory.getLog(RegisterRoomSQLManager.class);
	
	/**
	 * get room list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void getRoomsSQL(RegisterRoomForm form, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" ROOM.ROOM_NO, ")
		.append(" ROOM.ROOM_NAME, ")
		.append(" ROOM.ROOM_TYPE, ")
		.append(" CLS1.NAME AS ROOM_TYPE_NAME, ")
		.append(" ROOM.DESCRIPTION, ")
		.append(" ROOM.STATUS, ")
		.append(" CLS2.NAME AS STATUS_NAME, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.RENT_TYPE, ")
		.append(" CLS3.NAME AS RENT_TYPE_NAME, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS4.NAME AS MONEY_TYPE_NAME, ")
		.append(" PRICE.PRICE ")
		
		.append(" FROM MSTROOM ROOM ")
		
		.append(" LEFT OUTER JOIN MSTROOMPRICE PRICE ")
		.append(" ON PRICE.ROOM_NO = ROOM.ROOM_NO AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS1 ")
		.append(" ON CLS1.KEY1 = 'ROOM_TYPE' AND CLS1.VALUE = ROOM.ROOM_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS2 ")
		.append(" ON CLS2.KEY1 = 'ROOM_STATUS' AND CLS2.VALUE = ROOM.STATUS ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS3 ")
		.append(" ON CLS3.KEY1 = 'RENT_TYPE' AND CLS3.VALUE = PRICE.RENT_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS4 ")
		.append(" ON CLS4.KEY1 = 'MONEY_TYPE' AND CLS4.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" WHERE ROOM.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND ROOM.ROOM_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND ROOM.ROOM_NO <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getRoomType())){
			buf.append(" AND ROOM.ROOM_TYPE = ? ");
		}
		
		buf.append(" ORDER BY ROOM.ROOM_NAME, PRICE.RENT_TYPE, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("ROOM_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("ROOM_NO <= " + form.getNoEnd());
			}
			
			if (!Utils.isNullEmpty(form.getRoomType())){
				ps.setString(i++, form.getRoomType());
				log.info("ROOM_TYPE = " + form.getRoomType());
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeletePriceListSQL
	 * @param roomNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeletePriceListSQL(String roomNo, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTROOMPRICE SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE ROOM_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteRoomSQL
	 * @param roomNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeleteRoomSQL(String roomNo, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTROOM SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE ROOM_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterPriceSQL
	 * @param roomNo
	 * @param price
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterPriceSQL(String roomNo, MstRoomPriceDTO price, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTROOMPRICE ( ")
		.append(" ROOM_NO, RENT_TYPE, MONEY_TYPE, PRICE, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			ps.setString(i++, price.getRentType());
			log.info("RENT_TYPE = " + price.getRentType());
			
			ps.setString(i++, price.getMoneyType());
			log.info("MONEY_TYPE = " + price.getMoneyType());
			
			ps.setString(i++, price.getPrice());
			log.info("PRICE = " + price.getPrice());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterRoomSQL
	 * @param room
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterRoomSQL(MstRoomDTO room, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTROOM ( ")
		.append(" ROOM_NAME, ROOM_TYPE, DESCRIPTION, STATUS, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, '0', ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, room.getRoomName());
			log.info("ROOM_NAME = " + room.getRoomName());
			
			ps.setString(i++, room.getRoomType());
			log.info("ROOM_TYPE = " + room.getRoomType());
			
			ps.setString(i++, room.getDescription());
			log.info("DESCRIPTION = " + room.getDescription());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateRoomSQL
	 * @param room 
	 * @param user 
	 * @param conn 
	 * @throws ServerException 
	 */
	public void makeUpdateRoomSQL(MstRoomDTO room, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTROOM SET ")
		.append(" ROOM_NAME = ?, ROOM_TYPE = ?, DESCRIPTION = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		.append(" AND ROOM_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, room.getRoomName());
			log.info("ROOM_NAME = " + room.getRoomName());
			
			ps.setString(i++, room.getRoomType());
			log.info("ROOM_TYPE = " + room.getRoomType());
			
			ps.setString(i++, room.getDescription());
			log.info("DESCRIPTION = " + room.getDescription());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, room.getRoomNo());
			log.info("ROOM_NO = " + room.getRoomNo());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * getMaxRoomNoSQL
	 * @param conn
	 * @throws ServerException 
	 */
	public void getMaxRoomNoSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(ROOM_NO) AS ROOM_NO ")
		.append(" FROM MSTROOM ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * update room status
	 * @param roomNo
	 * @param reserveStatus
	 * @param conn 
	 * @param user 
	 * @throws ServerException
	 */
	@Override
	public void makeUpdateRoomStatus(String roomNo, String reserveStatus,
			LoginDTO user, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTROOM SET ")
		.append(" STATUS = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		.append(" AND ROOM_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_BOOKING)){
				ps.setString(i++, Const.SYSTEM_CONFIG.ROOM_STATUS_BOOKED);
				log.info("STATUS = " + Const.SYSTEM_CONFIG.ROOM_STATUS_BOOKED);
			} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_IN_USE)){
				ps.setString(i++, Const.SYSTEM_CONFIG.ROOM_STATUS_IN_USE);
				log.info("STATUS = " + Const.SYSTEM_CONFIG.ROOM_STATUS_IN_USE);
			} else if (reserveStatus.equals(Const.SYSTEM_CONFIG.RESERVE_STATUS_FINISHED)){
				ps.setString(i++, Const.SYSTEM_CONFIG.ROOM_STATUS_FREE);
				log.info("STATUS = " + Const.SYSTEM_CONFIG.ROOM_STATUS_FREE);
			}
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * reset room status to free
	 * @param roomNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeResetRoomStatus(String roomNo, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTROOM SET ")
		.append(" STATUS = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		.append(" AND ROOM_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, Const.SYSTEM_CONFIG.ROOM_STATUS_FREE);
			log.info("STATUS = " + Const.SYSTEM_CONFIG.ROOM_STATUS_FREE);
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
}
